﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using MSExcel = Microsoft.Office.Interop.Excel;
using System.IO;
using System.Reflection;
using System.Windows.Forms;
using System.Data;
using System.Data.OleDb;
using System.Data.SqlClient;

namespace PDNPAS
{
    class ExcelFiles
    {
        MSExcel.Application excelApp = null;              //Excel应用程序变量
        MSExcel.Workbook excelDoc = null;                     //Excel文档变量

        public MSExcel.Application ExcelApp
        {
            get
            {
                return excelApp;
            }
            set
            {
                excelApp = value;
            }
        }
        public MSExcel.Workbook ExcelDoc
        {
            get
            {
                return excelDoc;
            }
            set
            {
                excelDoc = value;
            }
        }


        public void CreateNewExcel(string fileLocation)//@"C:\MyExcel.xlsx"
        {
            object path = fileLocation;                     //路径
            excelApp = new MSExcel.Application();     //初始化
            if (File.Exists((string)path)) //如果已存在，则删除
            {
                File.Delete((string)path);
            }
            //由于使用的是COM库，因此有许多变量需要用Nothing代替
            Object Nothing = Missing.Value;
            excelDoc = excelApp.Workbooks.Add(Nothing);
        }

        public void CreateNewExcel()//@"C:\MyExcel.xlsx"
        {

            excelApp = new MSExcel.Application();     //初始化
            //由于使用的是COM库，因此有许多变量需要用Nothing代替
            Object Nothing = Missing.Value;
            excelDoc = excelApp.Workbooks.Add(Nothing);
        }

        public void CreateNewExcelFromTemplate(string TemplateLocation)//@"C:\MyExcel.xlsx"
        {
            object path = TemplateLocation;     //模板路径
            excelApp = new MSExcel.Application();     //初始化
            if (!File.Exists((string)path)) //如果不存在
            {
                MessageBox.Show("模板不存在！");
                return;
            }

            Object Nothing = Missing.Value;
            excelDoc = excelApp.Workbooks._Open(TemplateLocation,
                Missing.Value, Missing.Value, Missing.Value, Missing.Value,
                Missing.Value, Missing.Value, Missing.Value, Missing.Value,
                Missing.Value, Missing.Value, Missing.Value, Missing.Value);
        }

        public void OpenExcel(string fileLocation)
        {

            object MissingValue = Type.Missing;
            excelApp = new MSExcel.Application();
            excelDoc = excelApp.Workbooks.Open(fileLocation, MissingValue,
                MissingValue, MissingValue, MissingValue,
                MissingValue, MissingValue, MissingValue,
                MissingValue, MissingValue, MissingValue,
                MissingValue, MissingValue, MissingValue,
                MissingValue);
        }


        public void SaveExcelFile(string fileLocation)//@"C:\MyExcel.xlsx"
        {
            object path = fileLocation;
            //WdSaveFormat为Excel文档的保存格式
            Object Nothing = Missing.Value;
            object format;
            if (System.IO.Path.GetExtension(fileLocation).ToLower() == ".xlsx")
            {
                format = MSExcel.XlFileFormat.xlOpenXMLWorkbook;//Excel 2007版本
            }
            else if (System.IO.Path.GetExtension(fileLocation).ToLower() == ".xls")
            {
                format = MSExcel.XlFileFormat.xlExcel8;//excel 2003版本           
            }
            else
            {
                format = MSExcel.XlFileFormat.xlWorkbookDefault;
            }
            //将excelDoc文档对象的内容保存为excel文档 
            excelDoc.SaveAs(path, format, Nothing, Nothing, Nothing, Nothing, MSExcel.XlSaveAsAccessMode.xlExclusive, Nothing, Nothing, Nothing, Nothing, Nothing);
            //关闭excelDoc文档对象 
            excelDoc.Close(true, Nothing, Nothing);
            //关闭excelApp组件对象 
            excelApp.Quit();
            KillExcelProgress();
        }

        public int SaveExcelFile()
        {
            if (excelDoc.Path == "")
            {
                MessageBox.Show("请指定存储路径！");
                return 0;
            }
            else
            {
                excelDoc.Save();
                excelDoc.Close(true, null, null);
                excelApp.Quit();
                KillExcelProgress();
                return 1;
            }
        }

        public void InsertExcel(int sheetNum, int column, int row, string value)//@"C:\MyExcel.xlsx"
        {
            Object Nothing = Missing.Value;
            //使用第一个工作表作为插入数据的工作表
            MSExcel.Worksheet ws = (MSExcel.Worksheet)excelDoc.Sheets[sheetNum];

            ws.Cells[column, row] = value;

            ////声明一个MSExcel.Range 类型的变量r
            //MSExcel.Range r;
            ////获得A1处的表格，并赋值
            //r = ws.get_Range("A1", "A1");//有待改进
            //r.Value2 = value;
        }

        public string ReadExcelCell(int sheetNum, int column, int row)//@"C:\MyExcel.xlsx"
        {
            Object Nothing = Missing.Value;
            //使用第一个工作表作为插入数据的工作表
            MSExcel.Worksheet ws = (MSExcel.Worksheet)excelDoc.Sheets[sheetNum];

            return ws.Cells[column, row].ToString() ;

            ////声明一个MSExcel.Range 类型的变量r
            //MSExcel.Range r;
            ////获得A1处的表格，并赋值
            //r = ws.get_Range("A1", "A1");//有待改进
            //r.Value2 = value;
        }

        public DataSet GetDataSet(string fileLocation)//对excel中空的操作.是DBnull,不是null.
        {
            string strConnection = "";
            if (System.IO.Path.GetExtension(fileLocation).ToLower() == ".xlsx")//Excel 2007版本
            {
                strConnection = "Provider=Microsoft.ACE.OLEDB.12.0;";
                strConnection += "Data Source=" + fileLocation;
                strConnection += ";Extended Properties=\"Excel 12.0 Xml;HDR=YES\";";
            }
            else
            {
                strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;";
                strConnection += "Data Source=" + fileLocation + ";";
                strConnection += "Extended Properties='Excel 8.0;HDR=Yes;IMEX=1'";

            }

            OleDbConnection objConnection = new OleDbConnection(strConnection);
            objConnection.Open();
            OleDbDataAdapter myCommandd = new OleDbDataAdapter("select * from [Sheet1$]", objConnection);
            DataSet ds = new DataSet();
            myCommandd.Fill(ds, "[Sheet1$]");
            objConnection.Close();
            return ds;
        }

        public DataTable GetDataTable(string fileLocation)
        {
            string strConnection = "";
            if (System.IO.Path.GetExtension(fileLocation).ToLower() == ".xlsx")//Excel 2007版本
            {
                strConnection = "Provider=Microsoft.ACE.OLEDB.12.0;";
                strConnection += "Data Source=" + fileLocation;
                strConnection += " Extended Properties=\"Excel 12.0 Xml;HDR=YES\";";
            }
            else
            {
                strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;";
                strConnection += "Data Source=" + fileLocation + ";";
                strConnection += "Extended Properties='Excel 8.0;HDR=Yes;IMEX=1'";

            }

            OleDbConnection objConnection = new OleDbConnection(strConnection);
            objConnection.Open();
            OleDbDataAdapter myCommandd = new OleDbDataAdapter("select * from [Sheet1$]", objConnection);
            DataSet ds = new DataSet();
            myCommandd.Fill(ds, "[Sheet1$]");
            DataTable dt = ds.Tables["[Sheet1$]"];
            objConnection.Close();
            return dt;
        }



        public void KillExcelProgress()//杀死excel进程
        {
            //System.Runtime.InteropServices.Marshal.ReleaseComObject(wsheet);
            System.Runtime.InteropServices.Marshal.ReleaseComObject(excelDoc);
            System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp);
            //wsheet = null;
            excelDoc = null;
            excelApp = null;
            GC.Collect();
        }
    }
}
